<%@ Page language="vb" CodeFile="searchVehicleServiceBudget.aspx.vb" Inherits="searchVehicleServiceBudget_aspx_vb" %>

<%
    rowNumber = 0
    realtime = Now()
    realtime = Left(realtime, 10)
    If (Request.QueryString("do")) = Nothing Then 
        subDo = Request.QueryString("subDo")
        Over()
        dbOpen()
        RecSet = Conn.Execute("Select partnerID, partnerName, countryID From tblPartner Order by partnerName")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                partnerID = RecSet.Fields("partnerID").Value
                partnerName = RecSet.Fields("partnerName").Value
                countryID = RecSet.Fields("countryID").Value
                strSelectSubJS = strSelectSubJS + "new Array(" & partnerID & "," & countryID & ",""" & partnerName & """),"
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        dbOpen()
        RecSet = Conn.Execute("Select countryID From tblCountry Order by countryID")
        Do Until RecSet.EOF
            countryID = RecSet.Fields("countryID").Value
            strSelectSubJS = strSelectSubJS + "new Array(0," & countryID & ","" ------------------------------------  ""),"
            RecSet.MoveNext()
        Loop
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        strSelectSubJS = Left(strSelectSubJS, Len(strSelectSubJS) - 1)
        strSelectSubHeadline = "Select partner"
        selectSub()
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='1000'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>REPORT OF VEHICLES SERVICE BUDGET<hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='searchVehicleServiceBudget.aspx?do=search' method='post' name='store_productsSearch'>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Country:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='countryID' id='countryID' onchange=""set_child_listbox(this, document.store_productsSearch.partnerID,Product,'Products');"">")
        Response.Write("<option value=''> Select country  ")
        dbOpen()
        RecSet = Conn.Execute("Select countryID, country From tblCountry")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                countryID = RecSet.Fields("countryID").Value
                country = RecSet.Fields("country").Value
                Response.Write("<option value='" & countryID & "'> " & country & " ")
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Partner:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='partnerID' id='partnerID' disabled>")
        Response.Write("<option value=''> - Select partner - ")
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Lease contract status:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='status' id='status'>")
        Response.Write("<option value=''> - Select status - ")
        Response.Write("<option value='active'> Active ")
        Response.Write("<option value='inactive'> Inactive ")
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Generate report'></td></tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("</table>")
        Under()
    ElseIf Request.QueryString("do") = "search" Then 
        subDo = Request.QueryString("subDo")
        countryID = Request.Form("countryID")
        partnerID = Request.Form("partnerID")
        status = Request.Form("status")
        If Len(countryID) = 0 Or Len(partnerID) = 0 Then 
            countryID = Request.QueryString("countryID")
            partnerID = Request.QueryString("partnerID")
            status = Request.QueryString("status")
        End IF
        If Len(countryID) = 0 Or Len(partnerID) = 0 Then 
            Response.Redirect("searchVehicleEstimatedKms.aspx")
        Else
            countryID = CLng(countryID)
            partnerID = CLng(partnerID)
        End IF
        If status = "active" Then 
            sqlStatus = " And v.active = 1 And v.completed = 0"
        ElseIf status = "inactive" Then 
            sqlStatus = " And (v.active = 0 Or v.completed = 1)"
        End IF
        dbOpen()
        RecSet = Conn.Execute("Select p.partnerName, c.countryCurrency, c.decimals From (tblPartner p INNER JOIN tblCountry c ON c.countryID = p.countryID) Where p.partnerID = " & partnerID & "")
        partnerName = RecSet.Fields("partnerName").Value
        countryCurrency = RecSet.Fields("countryCurrency").Value
        decimals = RecSet.Fields("decimals").Value
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        flushCounter = 0
        If subDo = "print" Then 
            OverPrint()
        Else
            Over()
        End IF
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='100%'>")
        Response.Write("<tr><td><br></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
        Response.Write("<tr>")
        Response.Write("<td class='headline' colspan='19'>")
        Response.Write("REPORT OF VEHICLES SERVICE BUDGET, " & UCase(partnerName) & "")
        If Len(subDo) = 0 Then 
            Response.Write("<a href='searchVehicleServiceBudget.aspx?do=search&subDo=print&partnerID=" & partnerID & "&countryID=" & countryID & "&status=" & status & "' target='_blank'><img src='gui/printer.gif' align='right' alt='Printable version' border='0'></a>")
        End IF
        Response.Write("<hr class='hrHeadline'>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr bgcolor='#eeeeee'>")
        Response.Write("<td class='formBold'>Licence number</td>")
        'Response.Write "<td class='formBold'>VIN-number</td>"
        Response.Write("<td class='formBold'>Contract nr</td>")
        Response.Write("<td class='formBold'>Status</td>")
        Response.Write("<td class='formBold'>Product</td>")
        Response.Write("<td class='formBold'>Make & model</td>")
        Response.Write("<td class='formBold'>Lease taker</td>")
        Response.Write("<td class='formBold'>Lease start date</td>")
        Response.Write("<td class='formBold'>Lease end date</td>")
        Response.Write("<td class='formBold'>Last invoiced period</td>")
        Response.Write("<td class='formBold'>price / month VAT 0% (" & countryCurrency & ")</td>")
        Response.Write("<td class='formBold'>Price cat.</td>")
        Response.Write("<td class='formBold'>Unpaid claim amount VAT 0% (" & countryCurrency & ")</td>")
        Response.Write("<td class='formBold'>Paid claim amount VAT 0% (" & countryCurrency & ")</td>")
        Response.Write("<td class='formBold'>Service budget VAT 0% (" & countryCurrency & ")</td>")
        Response.Write("<td class='formBold'>Duration km</td>")
        Response.Write("<td class='formBold'>Last known kms</td>")
        Response.Write("<td class='formBold'>Est. kms</td>")
        Response.Write("<td class='formBold'>Est. service budget VAT 0% (" & countryCurrency & ")</td>")
        If Len(subDo) = 0 Then 
            Response.Write("<td class='formBold' align='right'></td>")
        End IF
        Response.Write("</tr>")
        rowNumber = 1
        dbOpen()
        RecSet = Conn.Execute("Select v.vehicleID, v.regnr, v.vinNumber, v.contractNumber, v.leaseStartDate, v.consumedDistance As vehicleKms, v.active, v.completed, b.brand, m.model, c.client, pl.price, pl.priceListDuration, pl.priceListDistance, pl.priceListCategory, p.product, (Select Sum(cl.paymentAmount) From tblClaim cl Where cl.vehicleID = v.vehicleID) As paidClaimSum, (Select Sum(cl.authorizedAmount) From tblClaim cl Where cl.vehicleID = v.vehicleID And cl.paymentDate Is Null) As unpaidClaimSum, (Select Count(vehicleID) From tblInvoiceContent Where vehicleID = v.vehicleID) As invoiceCount, (Select Count(vehicleID) From tblInvoiceCreditContent Where vehicleID = v.vehicleID) As creditCount, (Select MAX(cl.currentDistance) From tblClaim cl Where cl.vehicleID = v.vehicleID) As latestClaimKms, (Select MAX(cl.date) From tblClaim cl Where cl.vehicleID = v.vehicleID) As latestClaimDate From (((((tblVehicle v INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblClient c ON c.clientID = v.clientID) INNER JOIN tblPriceList pl ON pl.priceListID = v.priceListID) INNER JOIN tblProduct p ON p.productID = v.productID) Where (Select Count(ic.vehicleID) From tblInvoiceContent ic Where ic.vehicleID = v.vehicleID) >= 1 And v.partnerID = " & partnerID & sqlStatus & " Order By v.leaseStartdate")
        If RecSet.EOF Then 
            Response.Write("<tr><td><font color='red'>No results found</font></td></tr>")
        Else
            Do Until RecSet.EOF
                vehicleID = RecSet.Fields("vehicleID").Value
                regnr = RecSet.Fields("regnr").Value
                vinNumber = RecSet.Fields("vinNumber").Value
                contractNumber = RecSet.Fields("contractNumber").Value
                leaseStartDate = RecSet.Fields("leaseStartDate").Value
                vehicleKms = RecSet.Fields("vehicleKms").Value
                active = RecSet.Fields("active").Value
                completed = RecSet.Fields("completed").Value
                brand = RecSet.Fields("brand").Value
                model = RecSet.Fields("model").Value
                client = RecSet.Fields("client").Value
                price = RecSet.Fields("price").Value
                priceListDuration = RecSet.Fields("priceListDuration").Value
                priceListDistance = RecSet.Fields("priceListDistance").Value
                priceListCategory = RecSet.Fields("priceListCategory").Value
                product = RecSet.Fields("product").Value
                paidClaimSum = RecSet.Fields("paidClaimSum").Value
                unpaidClaimSum = RecSet.Fields("unpaidClaimSum").Value
                invoiceCount = RecSet.Fields("invoiceCount").Value
                creditCount = RecSet.Fields("creditCount").Value
                latestClaimKms = RecSet.Fields("latestClaimKms").Value
                latestClaimDate = RecSet.Fields("latestClaimDate").Value
                priceListDistance = CLng(priceListDistance)
                If CDbl(completed) = 1 Then 
                    status = "<font color='red'>Completed</font>"
                ElseIf CDbl(active) = 1 Then 
                    status = "<font color='green'>Active</font>"
                ElseIf CDbl(active) = 0 Then 
                    status = "<font color='red'>Inactive</font>"
                End IF
                leaseEndDate = DateAdd("m", CDbl(priceListDuration), Convert.ToDateTime(leaseStartDate))
                If Right(leaseStartDate, 2) <> "01" Then 
                    leaseEndDate = DateAdd("m", CDbl("1"), Convert.ToDateTime(leaseEndDate))
                End IF
                leaseEndDate = Left(leaseEndDate, 8) & "01"
                leaseEndDate = DateAdd("d", CDbl("-1"), Convert.ToDateTime(leaseEndDate))
                priceSum = CDbl(price) * (CDbl(invoiceCount) - CDbl(creditCount))
                If IsDBNull(unpaidClaimSum) Then 
                    unpaidClaimSum = 0
                End IF
                If IsDBNull(paidClaimSum) Then 
                    paidClaimSum = 0
                End IF
                serviceBudget = priceSum - CDbl(paidClaimSum) - CDbl(unpaidClaimSum)
                If CInt(serviceBudget) > 0 Then 
                    serviceBudget = "<font color='green'>+" & FormatNumber(CDbl(serviceBudget), decimals) & "</font>"
                Else
                    serviceBudget = "<font color='red'>" & FormatNumber(CDbl(serviceBudget), decimals) & "</font>"
                End IF
                priceListCategory = Left(priceListCategory, 1)
                If IsDBNull(latestClaimKms) Then 
                    latestClaimKms = 0
                End IF
                If IsDBNull(vehicleKms) Then 
                    vehicleKms = latestClaimKms
                ElseIf latestClaimKms > vehicleKms Then 
                    vehicleKms = latestClaimKms
                End IF
                If CInt(vehicleKms) = 0 Or IsDBNull(latestClaimDate) Then 
                    If CInt(vehicleKms) = 0 Then 
                        vehicleKms = "N/A"
                    End IF
                    latestClaimDate = "N/A"
                    estimatedKms = "N/A"
                    estimatedServiceBudget = "N/A"
                Else
                    leaseContractDays = DateDiff("d", Convert.ToDateTime(leaseStartDate), Convert.ToDateTime(leaseEndDate))
                    latestClaimDate = Left(latestClaimDate, 10)
                    currentDays = DateDiff("d", Convert.ToDateTime(leaseStartDate), Convert.ToDateTime(latestClaimDate))
                    dayPercentage = currentDays / leaseContractDays
                    If dayPercentage = 0 Then 
                        estimatedKms = 0
                    Else
                        estimatedKms = CDbl(vehicleKms) / dayPercentage
                    End IF
                    vehicleKms = FormatNumber(CDbl(vehicleKms), 0)
                    If estimatedKms > priceListDistance Then 
                        estimatedKms = "<font color='red'>" & FormatNumber(CDbl(estimatedKms), 0) & "</font>"
                    Else
                        estimatedKms = "<font color='green'>" & FormatNumber(CDbl(estimatedKms), 0) & "</font>"
                    End IF
                    priceTotSum = CDbl(priceListDuration) * CDbl(price)
                    If leaseEndDate > realtime Then 
                        currentDays = DateDiff("d", Convert.ToDateTime(leaseStartDate), Convert.ToDateTime(realtime))
                        dayPercentage = currentDays / leaseContractDays
                    Else
                        dayPercentage = 1
                    End IF
                    If dayPercentage = 0 Then 
                        estimatedClaimSum = 0
                    Else
                        estimatedClaimSum = CDbl((unpaidClaimSum + paidClaimSum)) / dayPercentage
                    End IF
                    estimatedServiceBudget = priceTotSum - estimatedClaimSum
                    If CInt(estimatedServiceBudget) > 0 Then 
                        estimatedServiceBudget = "<font color='green'>+" & FormatNumber(CDbl(estimatedServiceBudget), decimals) & "</font>"
                    Else
                        estimatedServiceBudget = "<font color='red'>" & FormatNumber(CDbl(estimatedServiceBudget), decimals) & "</font>"
                    End IF
                End IF
                If rowNumber = 0 Then 
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 1
                Else
                    strGrey = ""
                    rowNumber = 0
                End IF
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & regnr & "</td>")
                'Response.Write "<td>" & vinNumber & "</td>"
                Response.Write("<td>" & contractNumber & "</td>")
                Response.Write("<td>" & status & "</td>")
                Response.Write("<td>" & product & "</td>")
                Response.Write("<td>" & brand & " " & model & "</td>")
                Response.Write("<td>" & client & "</td>")
                Response.Write("<td>" & leaseStartDate & "</td>")
                Response.Write("<td>" & leaseEndDate & "</td>")
                Response.Write("<td align='right'>" & invoiceCount & "/" & priceListDuration & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(price), decimals) & "</td>")
                Response.Write("<td align='right'>" & priceListCategory & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(unpaidClaimSum), decimals) & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(paidClaimSum), decimals) & "</td>")
                Response.Write("<td align='right'>" & serviceBudget & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(priceListDistance), 0) & "</td>")
                Response.Write("<td align='right'>" & vehicleKms & "</td>")
                Response.Write("<td align='right'>" & estimatedKms & "</td>")
                Response.Write("<td align='right'>" & estimatedServiceBudget & "</td>")
                If Len(subDo) = 0 Then 
                    Response.Write("<td align='right'><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                End IF
                Response.Write("</tr>")
                If flushCounter = 100 Then 
                    Response.Flush()
                    flushCounter = 0
                Else
                    flushCounter = flushCounter + 1
                End IF
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='19'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("</table>")
        Under()
    End IF
%>
